clear
set more off

use "${wd}/nlrb/nlrb7799_1.dta",clear


drop      ///
	 idnlrb var* fiscalyear elect_id ///
	region type docket size_cat vote_los* union_l1* union_l2* ///
	duplic union_partic union_incumb rectype union_w_code address ///
	union_code casenum cntytext localnum pet_type employer unionname 


/////////////////////////////// Date Variables///////////////////////////////////////
// Date Closed
g year="19"+substr(dateclose,1,2)
g month=substr(dateclose,4,2)
g day=substr(dateclose,7,2)
replace day="1" if day==""
destring year month day,replace
g closing_date=mdy(month,day,year)
format closing_date %td
drop year month day 

// Date Filed
g year="19"+substr(datefile,1,2)
g month=substr(datefile,4,2)
g day=substr(datefile,7,2)
g check=0
foreach x of num 1967/1999{
	replace check=1 if year=="`x'"
}
replace year="" if check==0
replace day="" if day=="40" | day=="41" | day=="42"
replace day="" if day=="52" | day=="61" | day=="70" | day=="71"
replace month="" if month=="0["
replace month="" if month=="60" | month=="N3"
replace day="1" if year!="" & month!="" & day==""
destring year month day,replace
g date_filed=mdy(month,day,year)
format date_filed %td
drop year month day  check

// Date Election
g year="19"+substr(dateelec,1,2)
g month=substr(dateelec,4,2)
g day=substr(dateelec,7,2)
g check=0
foreach x of num 1967/1999{
	replace check=1 if year=="`x'"
}
replace year="" if check==0
replace month="" if month=="0S" | month=="0Q"
replace month="" if month=="0 " 
replace day="1" if year!="" & month!="" & day==""
destring year month day,replace
g date_election=mdy(month,day,year)
format date_election %td
drop year month day check

g year=year(closing_date)

g year_elect = year(date_election)

g diff = year-year_elect

drop dateclose dateelec datefile

// RETURN TO HANDLING THESE CASES
sort diff
order diff *date*

/////////////////////////////// Industry Variables///////////////////////////////////////

* problems
* 28X 28-
* 37X 37-
* 39- 39= 39W
* W29 17Z

* Neil's Industry Fix
gen sub1 = substr(sic3,1,1)
gen sub2 = substr(sic3,2,1)
gen sub3 = substr(sic3,3,1)
replace sub3="0" if sub3==""
replace sub3 = "0" if sub3=="-"
replace sub3 = "0" if sub3=="="
replace sub3 = "0" if sub3=="X"
replace sub3 = "0" if sub3=="Z"
replace sub3 = "3" if sub3=="W"
replace sub1 = "3" if sub1=="W"
gen ind = sub1+ sub2 + sub3
destring ind, replace
replace ind=. if ind==0
drop sub1-sub3 sic3






/////////////////////////////// Num Workers & Counts ///////////////////////////////////////
destring dispos,replace
drop if dispos==1 // 1 OBSERVATION
ren dispos closing_method
destring closing_method,replace
recode closing_method (3=4) (4=5)
// 1 is certified union won (certification of representatives)
// 2 is certified union lost (certification of results)
ren vnounion num_votes_against
ren eligible num_elig_employees
ren valid_tot num_valid_votes
ren valid_for_u num_votes_for

// Number of valid votes is missing before 1980
replace num_valid_votes=. if year<1980
replace num_votes_for=. if year<1980
replace num_votes_against=. if year<1980

// Get election majority
g uwin=1 if num_votes_for>num_votes_against
replace uwin=2 if num_votes_for==num_votes_against
replace uwin=3 if num_votes_for<num_votes_against

tab uwin outcome
tab uwin closing_method
drop outcome



/////////////////////////////// Geography ///////////////////////////////////////

// State 1 is the location of the employer's plant
// State 2 is the location of the Employer on the original petition
// County code/ city etc are the employer on the original petition
// I think this means that state1 is the state of the production unit

drop state1 state2fix state2 sttext stcodetext stcode
rename state1fix state1

replace state1 = "AL" if inlist(state1,"ALL","ALA")
replace state1 = "AR" if inlist(state1,"ARK")
replace state1 = "AK" if inlist(state1,"ALK")
replace state1 = "AZ" if inlist(state1,"ARI")
replace state1 = "CA" if inlist(state1,"CAL","CA-")
replace state1 = "CO" if inlist(state1,"COL")
replace state1 = "CT" if inlist(state1,"CON")
replace state1 = "DE" if inlist(state1,"DEL")
replace state1 = "FL" if inlist(state1,"FLA")
replace state1 = "HI" if inlist(state1,"HAW")
replace state1 = "ID" if inlist(state1,"IDA")
replace state1 = "ID" if inlist(state1,"IDO")
replace state1 = "IL" if inlist(state1,"ILL")
replace state1 = "IN" if inlist(state1,"IND")
replace state1 = "IA" if inlist(state1,"IOW")
replace state1 = "KS" if inlist(state1,"KS,KAN")
replace state1 = "MA" if inlist(state1,"MAS")
replace state1 = "MI" if inlist(state1,"MIC")
replace state1 = "MN" if inlist(state1,"MIN")
replace state1 = "MS" if inlist(state1,"MIS")
replace state1 = "MT" if inlist(state1,"MON")
replace state1 = "NE" if inlist(state1,"NEB")
replace state1 = "NV" if inlist(state1,"NEV")
replace state1 = "OH" if inlist(state1,"OHI","OH&")
replace state1 = "OK" if inlist(state1,"OKL")
replace state1 = "OR" if inlist(state1,"ORE")
replace state1 = "PA" if inlist(state1,"PEN")
replace state1 = "TN" if inlist(state1,"TEN")
replace state1 = "TX" if inlist(state1,"TEX")
replace state1 = "UT" if inlist(state1,"UTA")
replace state1 = "VT" if inlist(state1,"VER")
replace state1 = "WA" if inlist(state1,"WAS","W&S")
replace state1 = "WI" if inlist(state1,"WIS")
replace state1 = "WV" if inlist(state1,"WVA")
replace state1 = "WY" if inlist(state1,"WYO")



// Fix county codes
replace cntycode="163" if cntycode=="16"
replace cntycode="011" if cntycode=="11B"

ren cntycode cntycode_temp
ren state1 state1_temp

merge m:1 nlrb_id using "${temp}/nlrb_county_merge.dta"
drop if _merge == 2

replace cntycode = cntycode_temp if cntycode == ""
count if state1!=state1_temp & state1!=""
replace state1 = state1_temp if state1 == ""

// For non overlapping states (13 obs) the one merged in better aligns with the city
// Only one instance where the original state better aligns
replace state1 = "MT" if nlrb_id == "19-RC-13206"

*replace cntycode = "" if cntycode == "000"
*destring cntycode,replace


// Fill in county code for nlrb_id in same location but where one is missing the county!

replace cntycode = "" if cntycode == "000" 

save "${wd}/nlrb/nlrb7799_clean.dta",replace

